﻿using ExcelDataReader;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


internal class Excel
{

    ///表格数据集合
    private DataSet mResultSet;

    public Excel(string excelFile)
    {
        FileStream mStream = File.Open(excelFile, FileMode.Open, FileAccess.Read);
        IExcelDataReader mExcelReader = ExcelReaderFactory.CreateOpenXmlReader(mStream);
        mResultSet = mExcelReader.AsDataSet();
    }


    ///转换为Json
    ///<param name="JsonPath">Json文件路径</param>
    ///<param name="Header">表头行数</param>
    public void ConvertToJson(string JsonPath, Encoding encoding)
    {
        //判断Excel文件中是否存在数据表
        if (mResultSet.Tables.Count < 1)
            return;

        //默认读取第一个数据表
        DataTable mSheet = mResultSet.Tables[0];

        //判断数据表内是否存在数据
        if (mSheet.Rows.Count < 2)
            return;

        //读取数据表行数和列数
        int rowCount = mSheet.Rows.Count;
        int colCount = mSheet.Columns.Count;

        //准备一个列表存储整个表的数据
        List<Dictionary<string, object>> table = new List<Dictionary<string, object>>();

        //读取数据
        for (int i = 3; i < rowCount; i++)
        {
            //准备一个字典存储每一行的数据
            Dictionary<string, object> row = new Dictionary<string, object>();
            if(mSheet.Rows[i][0].ToString() == "")
                continue;

            for (int j = 1; j < colCount; j++)
            {
                if (mSheet.Rows[2][j].ToString() == "")
                    continue;
                //读取第一行数据作为表头字段
                string field = mSheet.Rows[1][j].ToString();
                string type = mSheet.Rows[2][j].ToString();
                var value = mSheet.Rows[i][j].ToString();
                switch (type)
                {
                    case "i[]":
                    case "l[]":
                    case "s[]":
                        
                    case "f":
                    case "d":
                    case "i":
                        row[field] = int.Parse(value);
                        break;
                    case "l":
                    case "s":
                        row[field] = value;
                        break;
                    default:
                        throw new Exception($"不支持此类型: {type}");
                }
            }

            //添加到类数据中
            table.Add(row);
        }

        //生成Json字符串
        string json = JsonConvert.SerializeObject(table, Newtonsoft.Json.Formatting.Indented);
        //写入文件
        using (FileStream fileStream = new FileStream(JsonPath, FileMode.Create, FileAccess.Write))
        {
            using (TextWriter textEriter = new StreamWriter(fileStream, encoding))
            {
                textEriter.Write(json);
            }
        }
    }

}



